import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

material_ts_box_router = APIRouter(prefix="/material/tsBox", tags=["管理柜资料管理"],
                                   dependencies=[Depends(oauth2_scheme)])


class ManagementCabinetSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fequipmentbarcode: Optional[str] = Field("", description="设备条码（支持前缀匹配）")
    fstorageflag: Optional[str] = Field("", description="存储标志编码")
    fstate: Optional[str] = Field("", description="状态编码")
    fstockno: Optional[str] = Field("", description="仓库编号")
    fpointNo: Optional[str] = Field("", description="点位编号")
    fmacno: Optional[str] = Field("", description="MAC地址（支持前缀匹配）")


@material_ts_box_router.get("/list", summary="管理柜资料查询接口")
def list_material_tsbox(search: ManagementCabinetSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_ts_box where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fequipmentbarcode != "":
        sql = sql + " and fequipmentbarcode like concat('%', :fequipmentbarcode ,'%')"
        param["fequipmentbarcode"] = search.fequipmentbarcode
    if search.fmacno != "":
        sql = sql + " and fmacno like concat('%', :fmacno ,'%')"
        param["fmacno"] = search.fmacno
    if search.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fstorageflag != "":
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = search.fstorageflag
    if search.fstate != "":
        sql = sql + " and fstate = :fstate "
        param["fstate"] = search.fstate
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fequipmentbarcode,feuipmentid,fequipmenttag,fsite_no,frfidip,"
    sql = sql + " frfidport,frfidid,fip,fport,flamp1,flamp2,flamp3,flamp4,"
    sql = sql + " flamp5,flamp6,fstate1,fstate2,fstate3,fstate4,fstate,fupddate,"
    sql = sql + " foperator,fmacno,fpoint_no,fpoint_size,frfidtype,fcabinettype"
    sql = sql + " from i_ts_box where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fequipmentbarcode != "":
        sql = sql + " and fequipmentbarcode like concat('%', :fequipmentbarcode ,'%')"
        param["fequipmentbarcode"] = search.fequipmentbarcode
    if search.fmacno != "":
        sql = sql + " and fmacno like concat('%', :fmacno ,'%')"
        param["fmacno"] = search.fmacno
    if search.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fstorageflag != "":
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = search.fstorageflag
    if search.fstate != "":
        sql = sql + " and fstate = :fstate "
        param["fstate"] = search.fstate
    if search.order == "desc":
        sql = sql + " order by  fpoint_no,fequipmentbarcode desc "
    else:
        sql = sql + " order by  fpoint_no,fequipmentbarcode "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fequipmentbarcode,b.fcatbarcode,b.fseq,b.fupddate,b.foperator,c.fequipmenttag"
    sql_3 = sql_3 + " from i_ts_box a,i_ts_controlbox b,i_ts_cabinet c  "
    sql_3 = sql_3 + " where a.fequipmentbarcode=b.fequipmentbarcode"
    sql_3 = sql_3 + " and b.fcatbarcode=c.fequipmentbarcode"
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fequipmentbarcode != "":
        sql_3 = sql_3 + " and a.fequipmentbarcode like concat('%', :fequipmentbarcode ,'%') "
        param["fequipmentbarcode"] = search.fequipmentbarcode
    if search.fmacno != "":
        sql_3 = sql_3 + " and a.fmacno like concat('%', :fmacno ,'%') "
        param["fmacno"] = search.fmacno
    if search.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fpointNo != "":
        sql_3 = sql_3 + " and a.fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fstorageflag != "":
        sql_3 = sql_3 + " and a.fstorageflag = :fstorageflag "
        param["fstorageflag"] = search.fstorageflag
    if search.fstate != "":
        sql_3 = sql_3 + " and a.fstate = :fstate "
        param["fstate"] = search.fstate
    sql_3 = sql_3 + " order by b.fequipmentbarcode,b.fseq"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fequipmentbarcode'] = data[key][0]
        d1['feuipmentid'] = data[key][1]
        d1['fequipmenttag'] = data[key][2]
        d1['fsiteNo'] = data[key][3]
        d1['frfidip'] = data[key][4]
        d1['frfidport'] = data[key][5]
        d1['frfidid'] = data[key][6]
        d1['fip'] = data[key][7]
        d1['fport'] = data[key][8]
        d1['flamp1'] = data[key][9]
        d1['flamp2'] = data[key][10]
        d1['flamp3'] = data[key][11]
        d1['flamp4'] = data[key][12]
        d1['flamp5'] = data[key][13]
        d1['flamp6'] = data[key][14]
        d1['fstate1'] = data[key][15]
        d1['fstate2'] = data[key][16]
        d1['fstate3'] = data[key][17]
        d1['fstate4'] = data[key][18]
        d1['fstate'] = data[key][19]
        d1['fupddate'] = data[key][20]
        d1['foperator'] = data[key][21]
        d1['fmacno'] = data[key][22]
        d1['fpointNo'] = data[key][23]
        d1['fpointsize'] = data[key][24]
        d1['frfidtype'] = data[key][25]
        d1['fcabinettype'] = data[key][26]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fequipmentbarcode'] = data_detail[j1][0]
                d2['fcatbarcode'] = data_detail[j1][1]
                d2['fseq'] = data_detail[j1][2]
                d2['fupddate'] = data_detail[j1][3]
                d2['foperator'] = data_detail[j1][4]
                d2['fequipmenttag'] = data_detail[j1][5]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class ManagementCabinetControlBoxDetail(BaseModel):
    """管理柜控制器明细"""
    fcatbarcode: str = Field(..., description="控制器条码")
    fequipmenttag: str = Field(..., description="设备标签")


class ManagementCabinetCreate(BaseModel):
    """管理柜新增请求参数"""
    fequipmentbarcode: str = Field(..., description="管理柜条码（唯一标识）")
    feuipmentid: str = Field("0", description="设备ID")
    fequipmenttag: str = Field("", description="设备标签")
    fsiteNo: str = Field("", description="站点编号")
    frfidip: str = Field("", description="RFID IP地址")
    frfidport: str = Field("", description="RFID 端口")
    frfidid: str = Field("0", description="RFID 编号")
    fip: str = Field("", description="IP地址")
    fport: str = Field("", description="端口号")
    flamp1: str = Field("", description="指示灯1状态")
    flamp2: str = Field("", description="指示灯2状态")
    flamp3: str = Field("", description="指示灯3状态")
    flamp4: str = Field("", description="指示灯4状态")
    flamp5: str = Field("", description="指示灯5状态")
    flamp6: str = Field("", description="指示灯6状态")
    fstate: str = Field("0", description="设备状态编码")
    fstate1: str = Field("0", description="状态1编码")
    fstate2: str = Field("0", description="状态2编码")
    fstate3: str = Field("0", description="状态3编码")
    fstate4: str = Field("0", description="状态4编码")
    fmacno: str = Field("", description="MAC地址")
    fpointNo: str = Field("", description="点位编号")
    fpointSize: str = Field("", description="点位尺寸")
    frfidtype: str = Field("0", description="RFID类型编码")
    fcabinettype: str = Field("0", description="柜体类型编码")
    details: List[ManagementCabinetControlBoxDetail] = Field(..., description="控制器明细列表")


@material_ts_box_router.post("/create", summary="管理柜新增接口")
def create_material_tsbox(create: ManagementCabinetCreate, db: Session = Depends(get_db),
                          token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fseq = 0
        fequipmentbarcode = create.fequipmentbarcode
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            details = create.details
            for detail in details:
                sql = "insert into i_ts_controlbox(fequipmentbarcode,fcatbarcode,fseq,fupddate,foperator)"
                sql = sql + " values( :fequipmentbarcode , :fcatbarcode , :fseq ,now(), :userid );"
                db.execute(text(sql), {
                    "fequipmentbarcode": fequipmentbarcode,
                    "fcatbarcode": detail.fcatbarcode,
                    "fseq": fseq,
                    "userid": user_id,
                })
                # 返回明细
                d2 = {}
                d2['fequipmentbarcode'] = fequipmentbarcode
                d2['fcatbarcode'] = detail.fcatbarcode
                d2['fequipmenttag'] = detail.fequipmenttag
                d2['fseq'] = fseq
                d2['fupddate'] = None
                d2['foperator'] = user_id
                detail_list.append(d2)
                fseq = fseq + 1
            sql = "insert into i_ts_box(fequipmentbarcode,feuipmentid,fequipmenttag,fsite_no,frfidip,"
            sql = sql + "frfidport,frfidid,fip,fport,flamp1,"
            sql = sql + "flamp2,flamp3,flamp4,flamp5,flamp6,"
            sql = sql + "fstate1,fstate2,fstate3,fstate4,fstate,"
            sql = sql + "fupddate,foperator,fmacno,fpoint_no,fpoint_size,"
            sql = sql + "frfidtype,fcabinettype)"
            sql = sql + " values( :fequipmentbarcode , :feuipmentid , :fequipmenttag , :fsiteNo , :frfidip "
            sql = sql + ", :frfidport , :frfidid , :fip , :fport , :flamp1 "
            sql = sql + ", :flamp2 , :flamp3 , :flamp4 , :flamp5 , :flamp6 "
            sql = sql + ", :fstate1 , :fstate2 , :fstate3 , :fstate4 , :fstate "
            sql = sql + ",now(), :userid , :fmacno , :fpointNo , :fpointSize "
            sql = sql + ", :frfidtype , :fcabinettype )"
            db.execute(text(sql), {
                "fequipmentbarcode": fequipmentbarcode,
                "feuipmentid": create.feuipmentid,
                "fequipmenttag": create.fequipmenttag,
                "fsiteNo": create.fsiteNo,
                "frfidip": create.frfidip,
                "frfidport": create.frfidport,
                "frfidid": create.frfidid,
                "fip": create.fip,
                "fport": create.fport,
                "flamp1": create.flamp1,
                "flamp2": create.flamp2,
                "flamp3": create.flamp3,
                "flamp4": create.flamp4,
                "flamp5": create.flamp5,
                "flamp6": create.flamp6,
                "fstate1": create.fstate1,
                "fstate2": create.fstate2,
                "fstate3": create.fstate3,
                "fstate4": create.fstate4,
                "fstate": create.fstate,
                "userid": user_id,
                "fmacno": create.fmacno,
                "fpointNo": create.fpointNo,
                "fpointSize": create.fpointSize,
                "frfidtype": create.frfidtype,
                "fcabinettype": create.fcabinettype,
            })

            # 返回
            d1['fequipmentbarcode'] = fequipmentbarcode
            d1['feuipmentid'] = create.feuipmentid
            d1['fequipmenttag'] = create.fequipmenttag
            d1['fsiteNo'] = create.fsiteNo
            d1['frfidip'] = create.frfidip
            d1['frfidport'] = create.frfidport
            d1['frfidid'] = create.frfidid
            d1['fip'] = create.fip
            d1['fport'] = create.fport
            d1['flamp1'] = create.flamp1
            d1['flamp2'] = create.flamp2
            d1['flamp3'] = create.flamp3
            d1['flamp4'] = create.flamp4
            d1['flamp5'] = create.flamp5
            d1['flamp6'] = create.flamp6
            d1['fstate1'] = create.fstate1
            d1['fstate2'] = create.fstate2
            d1['fstate3'] = create.fstate3
            d1['fstate4'] = create.fstate4
            d1['fstate'] = create.fstate
            d1['fupddate'] = None
            d1['foperator'] = user_id
            d1['fmacno'] = create.fmacno
            d1['fpointNo'] = create.fpointNo
            d1['fpointsize'] = create.fpointSize
            d1['frfidtype'] = create.frfidtype
            d1['fcabinettype'] = create.fcabinettype
            d1["details"] = detail_list
            details2 = []
            d1["details2"] = details2
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@material_ts_box_router.post("/update", summary="管理柜修改接口")
def update_material_tsbox(update: ManagementCabinetCreate, db: Session = Depends(get_db),
                          token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fseq = 0
        fequipmentbarcode = update.fequipmentbarcode
        with db.begin():
            sql = "delete from i_ts_controlbox where fequipmentbarcode= :fequipmentbarcode "
            db.execute(text(sql), {
                "fequipmentbarcode": fequipmentbarcode,
            })
            detail_list = []
            details = update.details
            for detail in details:
                sql = "insert into i_ts_controlbox(fequipmentbarcode,fcatbarcode,fseq,fupddate,foperator)"
                sql = sql + " values( :fequipmentbarcode , :fcatbarcode , :fseq ,now(), :userid );"
                db.execute(text(sql), {
                    "fequipmentbarcode": fequipmentbarcode,
                    "fcatbarcode": detail.fcatbarcode,
                    "fseq": fseq,
                    "userid": user_id,
                })
                fseq = fseq + 1
            sql = "update i_ts_box "
            sql = sql + " set fequipmenttag= :fequipmenttag ,frfidip= :frfidip ,frfidport= :frfidport "
            sql = sql + ",frfidid= :frfidid ,fip= :fip ,fport= :fport ,flamp1= :flamp1 "
            sql = sql + ",flamp2= :flamp2 ,flamp3= :flamp3 ,flamp4= :flamp4 ,flamp5= :flamp5 "
            sql = sql + ",fstate1= :fstate1 ,fstate2= :fstate2 ,fstate3= :fstate3 ,fstate4= :fstate4 "
            sql = sql + ",fstate= :fstate ,fupddate=now(),foperator= :userid ,fpoint_no= :fpointNo "
            sql = sql + ",fpoint_size= :fpointSize ,frfidtype= :frfidtype ,fcabinettype= :fcabinettype "
            sql = sql + " where fequipmentbarcode= :fequipmentbarcode  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fequipmenttag": update.fequipmenttag,
                "frfidip": update.frfidip,
                "frfidport": update.frfidport,
                "frfidid": update.frfidid,
                "fip": update.fip,
                "fport": update.fport,
                "flamp1": update.flamp1,
                "flamp2": update.flamp2,
                "flamp3": update.flamp3,
                "flamp4": update.flamp4,
                "flamp5": update.flamp5,
                "fstate1": update.fstate1,
                "fstate2": update.fstate2,
                "fstate3": update.fstate3,
                "fstate4": update.fstate4,
                "fstate": update.fstate,
                "userid": user_id,
                "fpointNo": update.fpointNo,
                "fpointSize": update.fpointSize,
                "frfidtype": update.frfidtype,
                "fcabinettype": update.fcabinettype,
                "fequipmentbarcode": update.fequipmentbarcode,
                "fsiteNo": update.fsiteNo,
            })
            return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@material_ts_box_router.post("/delete/{fequipmentbarcode}", summary="管理柜删除接口")
def delete_material_tsbox(fequipmentbarcode: str = Path(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from i_ts_controlbox where fequipmentbarcode= :fequipmentbarcode "
            db.execute(text(sql), {
                "fequipmentbarcode": fequipmentbarcode,
            })
            sql = "delete from i_ts_box where fequipmentbarcode= :fequipmentbarcode "
            db.execute(text(sql), {
                "fequipmentbarcode": fequipmentbarcode,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
